Public Class cCorso
    Inherits Connessione

    Private _id As String
    Private _anno As String
    Private _crediti As String
    Private _comune As String
    Private _descComune As String
    Private _provincia As String
    Private _tipo As String
    Private _dettaglio As String
    Private _titolo As String
    Private _giorni As String
    Private _ore As String
    Private _dataInizio As String
    Private _dataFine As String
    Private _ente As String
    Private _edizione As String
    Private _chiuso As Boolean
    Private _avvallo As Boolean
    Private _intEst As String



    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property
    Public Property anno() As String
        Get
            Return _anno
        End Get
        Set(ByVal Value As String)
            _anno = Value
        End Set
    End Property
    Public Property crediti() As String
        Get
            Return _crediti
        End Get
        Set(ByVal Value As String)
            _crediti = Value
        End Set
    End Property
    Public Property comune() As String
        Get
            Return _comune
        End Get
        Set(ByVal Value As String)
            _comune = Value
        End Set
    End Property
    Public Property descComune() As String
        Get
            Return _descComune
        End Get
        Set(ByVal Value As String)
            _descComune = Value
        End Set
    End Property
    Public Property provincia() As String
        Get
            Return _provincia
        End Get
        Set(ByVal Value As String)
            _provincia = Value
        End Set
    End Property
    Public Property tipo() As String
        Get
            Return _tipo
        End Get
        Set(ByVal Value As String)
            _tipo = Value
        End Set
    End Property
    Public Property dettaglio() As String
        Get
            Return _dettaglio
        End Get
        Set(ByVal Value As String)
            _dettaglio = Value
        End Set
    End Property
    Public Property titolo() As String
        Get
            Return _titolo
        End Get
        Set(ByVal Value As String)
            _titolo = Value
        End Set
    End Property
    Public Property giorni() As String
        Get
            Return _giorni
        End Get
        Set(ByVal Value As String)
            _giorni = Value
        End Set
    End Property
    Public Property ore() As String
        Get
            Return _ore
        End Get
        Set(ByVal Value As String)
            _ore = Value
        End Set
    End Property
    Public Property dataInizio() As String
        Get
            Return _dataInizio
        End Get
        Set(ByVal Value As String)
            _dataInizio = Value
        End Set
    End Property
    Public Property dataFine() As String
        Get
            Return _dataFine
        End Get
        Set(ByVal Value As String)
            _dataFine = Value
        End Set
    End Property
    Public Property ente() As String
        Get
            Return _ente
        End Get
        Set(ByVal Value As String)
            _ente = Value
        End Set
    End Property
    Public Property edizione() As String
        Get
            Return _edizione
        End Get
        Set(ByVal Value As String)
            _edizione = Value
        End Set
    End Property
    Public Property chiuso() As Boolean
        Get
            Return _chiuso
        End Get
        Set(ByVal Value As Boolean)
            _chiuso = Value
        End Set
    End Property
    Public Property avvallo() As Boolean
        Get
            Return _avvallo
        End Get
        Set(ByVal Value As Boolean)
            _avvallo = Value
        End Set
    End Property
    Public Property intEst() As String
        Get
            Return _intEst
        End Get
        Set(ByVal Value As String)
            _intEst = Value
        End Set
    End Property
    Public Sub carica()
        Dim sql As String = "select *, convert(char(10), datainizio, 103) as dataI, convert(char(10), dataFine, 103) as dataF from  Eventi where id=" & _id
        Dim dr As DataRow = getDataTable(sql).Rows(0)
        _anno = dr("anno")
        _crediti = Utility.sNull(dr("crediti"))
        _comune = Utility.sNull(dr("comune"))
        _descComune = Utility.sNull(dr("desc_Comune"))
        _provincia = Trim(dr("provincia"))
        _tipo = Utility.sNull(dr("tipo"))
        _dettaglio = Utility.sNull(dr("dettaglio"))
        _titolo = dr("titolo")
        _giorni = Utility.sNull(dr("giorni"))
        _ore = Utility.sNull(dr("ore"))
        _dataInizio = Utility.sNull(dr("dataI"))
        _dataFine = Utility.sNull(dr("dataF"))
        _ente = Utility.sNull(dr("ente"))
        _edizione = dr("edizione")
        _chiuso = dr("chiuso")
        _avvallo = dr("avvallo")
        _intEst = Utility.sNull(dr("intest"))


    End Sub

    Public Sub salva()
        If _id = "" Then
            inserisci()
        Else
            aggiorna()
        End If
    End Sub

    Private Sub inserisci()
        Dim sql As String = "insert into eventi (Anno, Crediti, Comune,desc_comune, Provincia, Tipo, Dettaglio, Titolo, Giorni, Ore, DataInizio, DataFine, Ente, Edizione, Chiuso, utente,avvallo, intest) " & _
            " values (@Anno, @Crediti, @Comune, @desc_comune, @Provincia, @Tipo, @Dettaglio, @Titolo, @Giorni, @Ore, @DataInizio, @DataFine, @Ente, @Edizione, @Chiuso, @Utente,@avvallo, @intest)"
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.AddWithValue("@anno", IIf(_anno = "", SqlTypes.SqlInt16.Null, _anno))
        cmd.Parameters.AddWithValue("@crediti", IIf(_crediti = "", SqlTypes.SqlInt16.Null, _crediti))
        cmd.Parameters.AddWithValue("@comune", IIf(_comune = "", SqlTypes.SqlInt16.Null, _comune))
        cmd.Parameters.AddWithValue("@desc_comune", _descComune)
        cmd.Parameters.AddWithValue("@provincia", _provincia)
        cmd.Parameters.AddWithValue("@tipo", IIf(_tipo = "", SqlTypes.SqlInt16.Null, _tipo))
        cmd.Parameters.AddWithValue("@dettaglio", IIf(_dettaglio = "", SqlTypes.SqlInt16.Null, _dettaglio))
        cmd.Parameters.AddWithValue("@titolo", _titolo)
        cmd.Parameters.AddWithValue("@giorni", IIf(_giorni = "", SqlTypes.SqlInt16.Null, _giorni))
        cmd.Parameters.AddWithValue("@ore", IIf(_ore = "", SqlTypes.SqlInt16.Null, _ore))
        cmd.Parameters.AddWithValue("@datainizio", IIf(_dataInizio = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataInizio)))
        cmd.Parameters.AddWithValue("@DataFine", IIf(_dataFine = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataFine)))
        cmd.Parameters.AddWithValue("@ente", IIf(_ente = "", SqlTypes.SqlInt16.Null, _ente))
        cmd.Parameters.AddWithValue("@edizione", _edizione)
        cmd.Parameters.AddWithValue("@chiuso", _chiuso)
        cmd.Parameters.AddWithValue("@avvallo", _avvallo)
        cmd.Parameters.AddWithValue("@intest", _intEst)
        cmd.Parameters.AddWithValue("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Private Sub aggiorna()
        Dim sql As String = "update eventi set Anno=@anno, Crediti=@crediti, Comune=@comune, desc_comune=@desc_comune, Provincia=@provincia, Tipo=@tipo, Dettaglio=@dettaglio, Titolo=@titolo, " & _
            "Giorni=@giorni, Ore=@ore, DataInizio=@dataInizio, DataFine=@dataFine, Ente=@ente, Edizione=@edizione, Chiuso=@chiuso, avvallo=@avvallo, intest=@intest, utente=@utente  where id = " & _id
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.AddWithValue("@anno", IIf(_anno = "", SqlTypes.SqlInt16.Null, _anno))
        cmd.Parameters.AddWithValue("@crediti", IIf(_crediti = "", SqlTypes.SqlInt16.Null, _crediti))
        cmd.Parameters.AddWithValue("@comune", IIf(_comune = "", SqlTypes.SqlInt16.Null, _comune))
        cmd.Parameters.AddWithValue("@desc_comune", _descComune)
        cmd.Parameters.AddWithValue("@provincia", _provincia)
        cmd.Parameters.AddWithValue("@tipo", IIf(_tipo = "", SqlTypes.SqlInt16.Null, _tipo))
        cmd.Parameters.AddWithValue("@dettaglio", IIf(_dettaglio = "", SqlTypes.SqlInt16.Null, _dettaglio))
        cmd.Parameters.AddWithValue("@titolo", _titolo)
        cmd.Parameters.AddWithValue("@giorni", IIf(_giorni = "", SqlTypes.SqlInt16.Null, _giorni))
        cmd.Parameters.AddWithValue("@ore", IIf(_ore = "", SqlTypes.SqlInt16.Null, _ore))
        cmd.Parameters.AddWithValue("@datainizio", IIf(_dataInizio = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataInizio)))
        cmd.Parameters.AddWithValue("@DataFine", IIf(_dataFine = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataFine)))
        cmd.Parameters.AddWithValue("@ente", IIf(_ente = "", SqlTypes.SqlInt16.Null, _ente))
        cmd.Parameters.AddWithValue("@edizione", _edizione)
        cmd.Parameters.AddWithValue("@chiuso", _chiuso)
        cmd.Parameters.AddWithValue("@avvallo", _avvallo)
        cmd.Parameters.AddWithValue("@intest", _intEst)
        cmd.Parameters.AddWithValue("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub


    Public Sub elimina()
        eseguiSql("update eventi set eliminato=1 where id=" & _id)
    End Sub


    Public Function getNumeroPartecipanti() As String
        Return getValore("SELECT COUNT(IscEventi.id) FROM IscEventi WHERE isceventi.stato=5 AND idEvento=" & _id)
    End Function


End Class

Public Class cIscEvento
    Inherits Connessione

    Private _id As String
    Private _idAna As String
    Private _idProf As String
    Private _nomeProf As String
    Private _dataNascita As String
    Private _matricola As String
    Private _tipo As String
    Private _stato As String
    Private _crediti As String
    Private _idEvento As String
    Private _dataI As String
    Private _dataF As String
    Private _ore As String
    Private _edizione As String
    Private _praticante As String

    Public Property praticante() As String
        Get
            Return _praticante
        End Get
        Set(ByVal Value As String)
            _praticante = Value
        End Set
    End Property

    Public Property matricola() As String
        Get
            Return _matricola
        End Get
        Set(ByVal Value As String)
            _matricola = Value
        End Set
    End Property
    Public Property dataNascita() As String
        Get
            Return _dataNascita
        End Get
        Set(ByVal Value As String)
            _dataNascita = Value
        End Set
    End Property

    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property
    Public Property idAna() As String
        Get
            Return _idAna
        End Get
        Set(ByVal Value As String)
            _idAna = Value
        End Set
    End Property
    Public Property idProf() As String
        Get
            Return _idProf
        End Get
        Set(ByVal Value As String)
            _idProf = Value
        End Set
    End Property
    Public Property tipo() As String
        Get
            Return _tipo
        End Get
        Set(ByVal Value As String)
            _tipo = Value
        End Set
    End Property
    Public Property stato() As String
        Get
            Return _stato
        End Get
        Set(ByVal Value As String)
            _stato = Value
        End Set
    End Property
    Public Property idEvento() As String
        Get
            Return _idEvento
        End Get
        Set(ByVal Value As String)
            _idEvento = Value
        End Set
    End Property
    Public Property crediti() As String
        Get
            Return _crediti
        End Get
        Set(ByVal Value As String)
            _crediti = Value
        End Set
    End Property
    Public ReadOnly Property dataI() As String
        Get
            Return _dataI
        End Get
    End Property
    Public ReadOnly Property dataF() As String
        Get
            Return _dataF
        End Get
    End Property
    Public ReadOnly Property ore() As String
        Get
            Return _ore
        End Get
    End Property
    Public ReadOnly Property edizione() As String
        Get
            Return _edizione
        End Get
    End Property
    Public Property nomeProf() As String
        Get
            Return _nomeProf
        End Get
        Set(ByVal Value As String)
            _nomeProf = Value
        End Set
    End Property

    Public Sub carica()
        Dim sql As String = "SELECT idEvento, idAna, idProf,nomeprof, IscEventi.crediti, IscEventi.tipo, stato, convert(char(10), datainizio, 103) as DataInizio,convert(char(10), DataFine, 103) as DataFine, Eventi.Ore, " & _
                      " Eventi.Edizione, convert(char(10), dataNascita,103) as dataN, matricola,note FROM   IscEventi INNER JOIN  Eventi ON IscEventi.idEvento = Eventi.id where IscEventi.id=" & _id
        Dim dr As DataRow = getDataTable(sql).Rows(0)

        _idEvento = dr("idevento")
        _idAna = Utility.sNull(dr("idAna"))
        _idProf = Utility.sNull(dr("idProf"))
        _nomeProf = Utility.sNull(dr("nomeProf"))
        _tipo = Utility.sNull(dr("tipo"))
        _stato = Utility.sNull(dr("stato"))
        _crediti = Utility.sNull(dr("crediti"))
        _dataI = Utility.sNull(dr("dataInizio"))
        _dataF = Utility.sNull(dr("dataFine"))
        _ore = Utility.sNull(dr("ore"))
        _edizione = Utility.sNull(dr("edizione"))
        _dataNascita = Utility.sNull(dr("dataN"))
        _matricola = Utility.sNull(dr("matricola"))
        _praticante = Utility.sNull(dr("note"))

    End Sub

    Public Sub salva()
        If _id = "" Then
            inserisci()
        Else
            aggiorna()
        End If
    End Sub

    Private Sub inserisci()
        Dim sql As String = "insert into iscEventi (idEvento, idAna, idProf, nomeProf, tipo, stato, crediti, utente, dataNascita, matricola, note) values (@idEvento, @idAna, @idProf, @nomeProf, @tipo, @stato, @crediti, @utente, @dataNascita, @matricola, @note)"

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.AddWithValue("@idEvento", _idEvento)
        cmd.Parameters.AddWithValue("@idAna", IIf(_idAna = "", SqlTypes.SqlInt16.Null, _idAna))
        cmd.Parameters.AddWithValue("@idProf", IIf(_idProf = "", SqlTypes.SqlInt16.Null, _idProf))
        cmd.Parameters.AddWithValue("@nomeProf", _nomeProf)
        cmd.Parameters.AddWithValue("@tipo", IIf(_tipo = "", SqlTypes.SqlInt16.Null, _tipo))
        cmd.Parameters.AddWithValue("@stato", IIf(_stato = "", SqlTypes.SqlInt16.Null, _stato))
        cmd.Parameters.AddWithValue("@crediti", IIf(_crediti = "", SqlTypes.SqlInt16.Null, _crediti))
        cmd.Parameters.AddWithValue("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)
        cmd.Parameters.AddWithValue("@dataNascita", IIf(_dataNascita = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataNascita)))
        cmd.Parameters.AddWithValue("@matricola", _matricola)
        cmd.Parameters.AddWithValue("@note", _praticante)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Private Sub aggiorna()
        Dim sql As String = "update iscEventi set idEvento=@idEvento, idAna=@idAna, idProf=@idProf, nomeProf = @nomeProf, tipo=@tipo, stato=@stato, " & _
            "crediti=@crediti, utente=@utente, dataNascita=@dataNascita, matricola=@matricola, note =@note where id = " & _id

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)

        cmd.Parameters.AddWithValue("@idEvento", _idEvento)
        cmd.Parameters.AddWithValue("@idAna", IIf(_idAna = "", SqlTypes.SqlInt16.Null, _idAna))
        cmd.Parameters.AddWithValue("@idProf", IIf(_idProf = "", SqlTypes.SqlInt16.Null, _idProf))
        cmd.Parameters.AddWithValue("@nomeProf", _nomeProf)
        cmd.Parameters.AddWithValue("@tipo", IIf(_tipo = "", SqlTypes.SqlInt16.Null, _tipo))
        cmd.Parameters.AddWithValue("@stato", IIf(_stato = "", SqlTypes.SqlInt16.Null, _stato))
        cmd.Parameters.AddWithValue("@crediti", IIf(_crediti = "", SqlTypes.SqlInt16.Null, _crediti))
        cmd.Parameters.AddWithValue("@Utente", System.Security.Principal.WindowsIdentity.GetCurrent.Name)
        cmd.Parameters.AddWithValue("@dataNascita", IIf(_dataNascita = "", SqlTypes.SqlDateTime.Null, Utility.FormattaDataxDB(_dataNascita)))
        cmd.Parameters.AddWithValue("@matricola", _matricola)
        cmd.Parameters.AddWithValue("@note", _praticante)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Sub elimina()
        eseguiSql("delete from iscEventi where id=" & _id)
    End Sub
End Class

Public Class cIscrizioni
    Inherits Connessione

    Private _cognome As String
    Private _evento As String

    Public WriteOnly Property cognome() As String
        Set(ByVal Value As String)
            _cognome = Value
        End Set
    End Property

    Public WriteOnly Property evento() As String
        Set(ByVal Value As String)
            _evento = Value
        End Set
    End Property


    Public Function elenco() As DataTable
        Dim sql As String = "SELECT IscEventi.id, IscEventi.crediti, Eventi.Titolo, nomeProf, convert(char(10), datanascita,103) as dataN FROM IscEventi INNER JOIN Eventi ON IscEventi.idEvento = Eventi.id"
        Dim sqlWhere As String = " where eventi.eliminato= 0 "

        If _cognome <> "" Then
            sqlWhere += " and nomeprof like '" & _cognome & "%'"
        End If

        If _evento <> "" Then
            sqlWhere += " and idEvento=" & _evento
        End If
        sql += sqlWhere
        sql += " order by nomeProf"

        Return getDataTable(sql)
    End Function

    Public Function elencoEventi() As DataTable
        Dim sql As String = "select null as id, '' as titolo union  select id, titolo + ' ' + edizione as titolo from eventi where eliminato=0 order by titolo"
        Return getDataTable(sql)
    End Function

    Public Function elencoTipo() As DataTable
        Dim sql As String = "select null as codice, '' as descrizione, 0 as crediti union  select codice, descrizione, crediti from tipoIscrizioneEvento order by descrizione"
        Return getDataTable(sql)
    End Function

    Public Function elencoStato() As DataTable
        Dim sql As String = "select null as codice, '' as descrizione union  select codice, descrizione from statoIscrizione "
        Return getDataTable(sql)
    End Function

 
End Class
